package net.phoenix.repository.dao.mysql;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import net.phoenix.repository.SQLParameter;
import net.phoenix.repository.dao.NodeTableQueryDao;
import net.phoenix.repository.data.NodeData;

/**
 * 
* Jigsaw server
* 
* @author    <a href="shamphone@gmail.com">Li XiongFeng</a>
* @date      2012-9-30
* @version   1.0.0
 */
public class MysqlNodeTableQueryDao extends NodeTableQueryDao {
	


	/**
	 * 获取指定父结点下的所有子节点，按照分页的模式
	 * 
	 * @param parentID
	 *            String
	 * @param fromIndex
	 *            int
	 * @param number
	 *            int
	 * @return NodeData[]
	 * @throws SQLException
	 */

	private static final String SQL_Find_By_Parent = "select * from NODE where PARENT_ID=? order by vindex";

	public NodeData[] findByParent(String parentID, long fromIndex, long number) throws SQLException {
		PreparedStatement command = null;
		String sql = toMysqlRangeQuery(SQL_Find_By_Parent);
		try {
			command = connection.prepareStatement(sql);
			command.setString(1, parentID);
			command.setLong(2, fromIndex);
			command.setLong(3, number);
			NodeData[] result = this.retrieve(command, sql);
			return result;
		} finally {
			this.close(command);
		}
	}

	private static final String SQL_Find_By_ParentRec = "select * from node n start with n.parent_id=? connect by n.parent_id=prior n.pkid "; // order
																																				// n.orderno";

	public NodeData[] findByParentRecursively(String parentID, long fromIndex, long number) throws SQLException {
		PreparedStatement command = null;
		String sql = toMysqlRangeQuery(SQL_Find_By_ParentRec);
		try {
			command = connection.prepareStatement(sql);
			command.setString(1, parentID);
			command.setLong(2, fromIndex);
			command.setLong(3, number);
			NodeData[] result = this.retrieve(command, sql);
			return result;
		} finally {
			this.close(command);
		}
	}

	private static final String SQL_Find_By_ParentAndNameRec = "select * from node n start with n.parent_id=? connect by n.parent_id=prior n.pkid and n.name=?";

	public NodeData[] findByParentRecursively(String parentID, String name, long fromIndex, long number) throws SQLException {
		PreparedStatement command = null;
		String sql = toMysqlRangeQuery(SQL_Find_By_ParentAndNameRec);
		try {
			command = connection.prepareStatement(sql);
			command.setString(1, parentID);
			command.setString(2, name);
			command.setLong(3, fromIndex );
			command.setLong(4, number);
			NodeData[] result = this.retrieve(command, sql);
			return result;
		} finally {
			this.close(command);
		}
	}

	/* (non-Javadoc)
	 * @see net.phoenix.repository.dao.ValueTableSearchDao#search(java.lang.String, net.phoenix.repository.SQLParameter[], long, long)
	 */
	@Override
	public NodeData[]  search(String query, SQLParameter[] parameters, long fromIndex, long number) throws SQLException {
		String sql = toMysqlRangeQuery(query);
		PreparedStatement statement = null;
		try {
			List<SQLParameter> all = new ArrayList<SQLParameter>();
			if (parameters != null)
				Collections.addAll(all, parameters);
			all.add(new SQLParameter(Types.BIGINT, new Long(fromIndex )));
			all.add(new SQLParameter(Types.BIGINT, new Long(number)));
			statement = this.createStatement(sql, (SQLParameter[]) all.toArray(new SQLParameter[all.size()]));
			return this.retrieve(statement, query);
		} finally {
			this.close(statement);
		}
	}
	/*
	public long count(String query, SQLParameter[] parameters) throws SQLException {
		long result = 0;
		PreparedStatement command = null;
		try {
			command = this.createStatement("select count(*) from ("+query+") as t ", parameters);
			result = this.queryLong(command);
			return result;
		} finally {
			this.close(command);
		}
	}
	*/

}

